package com.feather.tenia.adapter;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.feather.common.data.Column;
import com.feather.common.data.DataTable;
import com.feather.common.data.simple.SimpleColumn;
import com.feather.common.db.Connector;
import com.feather.common.db.DbSaver;
import com.feather.common.db.TypeMapping;
import com.feather.tenia.adapter.fetcher.BLOB_Fetcher;
import com.feather.tenia.adapter.fetcher.CLOB_Fetcher;
import com.feather.tenia.adapter.saver.ByteArray_BLOB;
import com.feather.tenia.adapter.saver.ByteArray_CLOB;
import com.feather.tenia.adapter.saver.NormalSaver;
import com.feather.tenia.adapter.saver.String_BLOB;
import com.feather.tenia.adapter.util.CloseUtil;
import com.feather.tenia.ExecuteCallback;
import com.feather.tenia.JdbcProperty;
import com.feather.tenia.MetaDataColumnHandler;
import com.feather.tenia.Procedure;
import com.feather.tenia.ProcedureMeta;
import com.feather.tenia.TeniaHelper;
import com.feather.tenia.Tenia;

/**
 * @author flogyin
 */
public abstract class TeniaAdapter implements Tenia, MetaDataColumnHandler {
	protected String						caption;
	protected Connector						connector;
	protected JdbcProperty					jdbcProperty;
	protected String						catalog;
	protected String						schema;
	protected boolean						iscacheProcedureColumn;
	protected boolean						ischeckFetcher;
	protected boolean						ischeckSaver;
	private Map<String, Integer>			sqlTypeMap			= new HashMap<String, Integer>();
	protected TypeMapping					typeMapping			= new TypeMappingAdapter();
	protected Map<String, Procedure>		procedureCache		= new HashMap<String, Procedure>();
	protected Map<String, Procedure>		sqlCache			= new HashMap<String, Procedure>();

	protected MetaDataColumnHandler			metaDataColumnHandler;
	private ExecuteCallback					overallExecuteCallback;
	private ThreadLocal<ExecuteCallback>	exeCallbackLocal	= new ThreadLocal<ExecuteCallback>();

	protected TeniaHelper					helper;
	protected boolean						placeholderEmptyEqualsNull;

	public TeniaAdapter(Connector connector, JdbcProperty jdbcProperty) {
		this.connector = connector;
		this.jdbcProperty = jdbcProperty;
		this.metaDataColumnHandler = this;

		setCatalog();
		setSchema();

		// [DbFetcher]
		this.typeMapping.register(new BLOB_Fetcher());
		this.typeMapping.register(new CLOB_Fetcher());

		// [DbSaver]
		this.typeMapping.register(new ByteArray_BLOB());// byte[] -> Types.BLOB
		this.typeMapping.register(new ByteArray_CLOB());// byte[] -> Types.CLOB
		this.typeMapping.register(new String_BLOB());// String -> Types.BLOB

		mapSqlType("bit", Types.BIT);// -7
		mapSqlType("tinyint", Types.TINYINT);// -6
		mapSqlType("smallint", Types.SMALLINT);// 5
		mapSqlType("int", Types.INTEGER);// 4
		mapSqlType("integer", Types.INTEGER);// 4
		mapSqlType("bigint", Types.BIGINT);// -5
		mapSqlType("float", Types.FLOAT);// 6
		mapSqlType("real", Types.REAL);// 7
		mapSqlType("double", Types.DOUBLE);// 8
		mapSqlType("numeric", Types.NUMERIC);// 2
		mapSqlType("decimal", Types.DECIMAL);// 3
		mapSqlType("char", Types.CHAR);// 1
		mapSqlType("varchar", Types.VARCHAR);// 12
		mapSqlType("longvarchar", Types.LONGVARCHAR);// -1
		mapSqlType("date", Types.DATE);// 91
		mapSqlType("time", Types.TIME);// 92
		mapSqlType("timestamp", Types.TIMESTAMP);// 93
		mapSqlType("binary", Types.BINARY);// -2
		mapSqlType("varbinary", Types.VARBINARY);// -3
		mapSqlType("longvarbinary", Types.LONGVARBINARY);// -4
		mapSqlType("null", Types.NULL);// 0
		// @since 1.2
		mapSqlType("other", Types.OTHER);// 1111
		mapSqlType("object", Types.JAVA_OBJECT);// 2000
		mapSqlType("distinct", Types.DISTINCT);// 2001
		mapSqlType("struct", Types.STRUCT);// 2002
		mapSqlType("array", Types.ARRAY);// 2003
		mapSqlType("blob", Types.BLOB);// 2004
		mapSqlType("clob", Types.CLOB);// 2005
		mapSqlType("ref", Types.REF);// 2006
		// @since 1.4
		mapSqlType("ref", Types.DATALINK);// 70
		mapSqlType("ref", Types.BOOLEAN);// 16
		// @since 1.6 (JDBC 4.0)
		mapSqlType("rowid", Types.ROWID);// -8
		mapSqlType("nchar", Types.NCHAR);// -15
		mapSqlType("nvarchar", Types.NVARCHAR);// -9
		mapSqlType("longnvarchar", Types.LONGNVARCHAR);// -16
		mapSqlType("nclob", Types.NCLOB);// 2011
		mapSqlType("sqlxml", Types.SQLXML);// 2009
	}

	@Override
	public String getCaption() {
		return this.caption;
	}

	@Override
	public void setCaption(String caption) {
		this.caption = caption;
	}

	@Override
	public String getCatalog() {
		return this.catalog;
	}

	@Override
	public String getSchema() {
		return this.schema;
	}

	@Override
	public Connector getConnector() {
		return this.connector;
	}

	@Override
	public boolean isCacheProcedureColumn() {
		return this.iscacheProcedureColumn;
	}

	public void setCacheProcedureColumn(boolean cache) {
		this.iscacheProcedureColumn = cache;
	}

	@Override
	public boolean isCheckFetcher() {
		return this.ischeckFetcher;
	}

	@Override
	public void setCheckFetcher(boolean check) {
		this.ischeckFetcher = check;
	}

	@Override
	public boolean isCheckSaver() {
		return this.ischeckSaver;
	}

	@Override
	public void setCheckSaver(boolean check) {
		this.ischeckSaver = check;
	}

	@Override
	public JdbcProperty getJdbcProperty() {
		return this.jdbcProperty;
	}

	@Override
	public TypeMapping getTypeMapping() {
		return this.typeMapping;
	}

	@Override
	public Map<String, Procedure> getProcedureCache() {
		return this.procedureCache;
	}

	@Override
	public boolean hasSqlTypeMap(String dataTypeName) {
		if (dataTypeName != null) {
			dataTypeName = dataTypeName.toLowerCase().trim();
			return this.sqlTypeMap.containsKey(dataTypeName);
		}
		return false;
	}

	@Override
	public int getSqlType(String dataTypeName) {
		if (dataTypeName != null) {
			dataTypeName = dataTypeName.toLowerCase().trim();
			Integer type = this.sqlTypeMap.get(dataTypeName);
			if (type != null)
				return type.intValue();
		}
		return Types.OTHER;
	}

	@Override
	public void mapSqlType(String dataTypeName, int dataType) {
		if (dataTypeName != null) {
			dataTypeName = dataTypeName.toLowerCase().trim();
			this.sqlTypeMap.put(dataTypeName, dataType);
		}
	}

	@Override
	public void clearSqlTypeMap() {
		this.sqlTypeMap.clear();
	}

	@Override
	public boolean execute(String sql) throws SQLException {
		ExecuteCallback callback = getExecuteCallback();
		if (callback != null) {
			String newSql = callback.onBeginSQL(this, sql, null);
			if (newSql != null)
				sql = newSql;
		}

		Statement stmt = null;
		boolean result = false;
		boolean complete = false;
		Connection connection = this.connector.getConnection();
		try {
			stmt = connection.createStatement();
			result = stmt.execute(sql);
			complete = true;
		} finally {
			CloseUtil.close(stmt, connection);
		}

		if (complete && callback != null) {
			callback.onCompleteSQL(this, sql, null);
		}
		return result;
	}

	@Override
	public boolean execute(String sql, Object[] parameters) throws SQLException {
		ExecuteCallback callback = getExecuteCallback();
		if (callback != null) {
			String newSql = callback.onBeginSQL(this, sql, parameters);
			if (newSql != null)
				sql = newSql;
		}

		int paramSize = parameters != null ? parameters.length : 0;
		List<Object> matchedParams = new ArrayList<Object>();
		SimpleColumn column = new SimpleColumn();

		ResultSet rs = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		boolean complete = false;
		Connection connection = this.connector.getConnection();
		try {
			pstmt = connection.prepareStatement(sql);
			for (int i = 0; i < paramSize; i++) {
				Object value = parameters[i];
				matchedParams.add(value);
				column.setOrdinal(i + 1);
				setPreparedStatement(pstmt, column, value);
			}
			result = pstmt.execute();
			complete = true;
		} finally {
			CloseUtil.close(rs, pstmt, connection);
		}

		if (complete && callback != null) {
			callback.onCompleteSQL(this, sql, matchedParams);
		}
		return result;
	}

	@Override
	public boolean execute(String sql, List<Object> parameters) throws SQLException {
		ExecuteCallback callback = getExecuteCallback();
		if (callback != null) {
			String newSql = callback.onBeginSQL(this, sql, parameters);
			if (newSql != null)
				sql = newSql;
		}

		int paramSize = parameters != null ? parameters.size() : 0;
		List<Object> matchedParams = new ArrayList<Object>();
		SimpleColumn column = new SimpleColumn();

		ResultSet rs = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		boolean complete = false;
		Connection connection = this.connector.getConnection();
		try {
			pstmt = connection.prepareStatement(sql);
			for (int i = 0; i < paramSize; i++) {
				Object value = parameters.get(i);
				matchedParams.add(value);
				column.setOrdinal(i + 1);
				setPreparedStatement(pstmt, column, value);
			}
			result = pstmt.execute();
			complete = true;
		} finally {
			CloseUtil.close(rs, pstmt, connection);
		}

		if (complete && callback != null) {
			callback.onCompleteSQL(this, sql, matchedParams);
		}
		return result;
	}

	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public boolean execute(String sql, Map<String, Object> parameters) throws SQLException {
		ExecuteCallback callback = getExecuteCallback();
		if (callback != null) {
			String newSql = callback.onBeginSQL(this, sql, parameters);
			if (newSql != null)
				sql = newSql;
		}

		Map<String, Object> matchedParams = new LinkedHashMap<String, Object>();
		Set paramSet = parameters != null ? parameters.entrySet() : null;

		PrepareExecuteSql peSql = PrepareExecuteSql.prepare(sql, paramSet, matchedParams, this.getPlaceholderEmptyEqualsNull());
		int phsize = peSql.placeholder.size();

		ResultSet rs = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		boolean complete = false;
		SimpleColumn column = new SimpleColumn();
		Connection connection = this.connector.getConnection();
		try {
			pstmt = connection.prepareStatement(peSql.prepareSql);
			for (int i = 0; i < phsize; i++) {
				String[] placeholderInfo = peSql.placeholder.get(i);
				String name = placeholderInfo[0];
				String type = placeholderInfo[1];
				Object value = parameterByPlaceholderName(name, paramSet);
				if (value instanceof String[]) {
					String[] arr = (String[]) value;
					value = arr.length > 0 ? arr[0] : null;
				}
				matchedParams.put(name, value);
				column.setOrdinal(i + 1);
				column.setName(name);
				column.setDataTypeName(type);
				setPreparedStatement(pstmt, column, value);
			}
			result = pstmt.execute();
			complete = true;
		} finally {
			CloseUtil.close(rs, pstmt, connection);
		}

		if (complete && callback != null) {
			callback.onCompleteSQL(this, peSql.prepareSql, matchedParams);
		}
		return result;
	}

	@Override
	public DataTable executeQuery(String sql) throws SQLException {
		ExecuteCallback callback = getExecuteCallback();
		if (callback != null) {
			String newSql = callback.onBeginSQL(this, sql, null);
			if (newSql != null)
				sql = newSql;
		}

		ResultSet rs = null;
		Statement stmt = null;
		DataTable dataTable = null;
		boolean complete = false;
		Connection connection = this.connector.getConnection();
		try {
			stmt = connection.createStatement();
			rs = stmt.executeQuery(sql);
			dataTable = getDataTable(stmt, rs);
			complete = true;
		} finally {
			CloseUtil.close(rs, stmt, connection);
		}

		if (complete && callback != null) {
			callback.onCompleteSQL(this, sql, null);
		}
		return dataTable;
	}

	@Override
	public DataTable executeQuery(String sql, Object[] parameters) throws SQLException {
		ExecuteCallback callback = getExecuteCallback();
		if (callback != null) {
			String newSql = callback.onBeginSQL(this, sql, null);
			if (newSql != null)
				sql = newSql;
		}

		int paramSize = parameters != null ? parameters.length : 0;
		List<Object> matchedParams = new ArrayList<Object>();
		SimpleColumn column = new SimpleColumn();

		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DataTable dataTable = null;
		boolean complete = false;
		Connection connection = this.connector.getConnection();
		try {
			pstmt = connection.prepareStatement(sql);
			for (int i = 0; i < paramSize; i++) {
				Object value = parameters[i];
				matchedParams.add(value);
				column.setOrdinal(i + 1);
				setPreparedStatement(pstmt, column, value);
			}
			rs = pstmt.executeQuery();
			dataTable = getDataTable(pstmt, rs);
			complete = true;
		} finally {
			CloseUtil.close(rs, pstmt, connection);
		}

		if (complete && callback != null) {
			callback.onCompleteSQL(this, sql, matchedParams);
		}
		return dataTable;
	}

	@Override
	public DataTable executeQuery(String sql, List<Object> parameters) throws SQLException {
		ExecuteCallback callback = getExecuteCallback();
		if (callback != null) {
			String newSql = callback.onBeginSQL(this, sql, null);
			if (newSql != null)
				sql = newSql;
		}

		int paramSize = parameters != null ? parameters.size() : 0;
		List<Object> matchedParams = new ArrayList<Object>();
		SimpleColumn column = new SimpleColumn();

		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DataTable dataTable = null;
		boolean complete = false;
		Connection connection = this.connector.getConnection();
		try {
			pstmt = connection.prepareStatement(sql);
			for (int i = 0; i < paramSize; i++) {
				Object value = parameters.get(i);
				matchedParams.add(value);
				column.setOrdinal(i + 1);
				setPreparedStatement(pstmt, column, value);
			}
			rs = pstmt.executeQuery();
			dataTable = getDataTable(pstmt, rs);
			complete = true;
		} finally {
			CloseUtil.close(rs, pstmt, connection);
		}

		if (complete && callback != null) {
			callback.onCompleteSQL(this, sql, matchedParams);
		}
		return dataTable;
	}

	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public DataTable executeQuery(String sql, Map<String, Object> parameters) throws SQLException {
		ExecuteCallback callback = getExecuteCallback();
		if (callback != null) {
			String newSql = callback.onBeginSQL(this, sql, null);
			if (newSql != null)
				sql = newSql;
		}

		Map matchedParams = new LinkedHashMap();
		Set paramSet = parameters != null ? parameters.entrySet() : null;

		PrepareExecuteSql peSql = PrepareExecuteSql.prepare(sql, paramSet, matchedParams, this.getPlaceholderEmptyEqualsNull());
		int phsize = peSql.placeholder.size();

		SimpleColumn column = new SimpleColumn();

		Connection connection = this.connector.getConnection();
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DataTable dataTable = null;
		boolean complete = false;
		try {
			pstmt = connection.prepareStatement(peSql.prepareSql);
			for (int i = 0; i < phsize; i++) {
				String[] placeholderInfo = peSql.placeholder.get(i);
				String name = placeholderInfo[0];
				String type = placeholderInfo[1];
				Object value = parameterByPlaceholderName(name, paramSet);
				if (value instanceof String[]) {
					String[] arr = (String[]) value;
					value = arr.length > 0 ? arr[0] : null;
				}
				matchedParams.put(name, value);
				column.setOrdinal(i + 1);
				column.setName(name);
				column.setDataTypeName(type);
				setPreparedStatement(pstmt, column, value);
			}
			rs = pstmt.executeQuery();
			dataTable = getDataTable(pstmt, rs);
			complete = true;
		} finally {
			CloseUtil.close(rs, pstmt, connection);
		}

		if (complete && callback != null) {
			callback.onCompleteSQL(this, peSql.prepareSql, matchedParams);
		}
		return dataTable;
	}

	protected Object parameterByPlaceholderName(String placeholderName, Set<Map.Entry<String, Object>> set) throws SQLException {
		for (Map.Entry<String, Object> entry : set) {
			String key = entry.getKey();
			if (key.equalsIgnoreCase(placeholderName))
				return entry.getValue();
		}
		throw new SQLException("Not found parameter value by: " + placeholderName);
	}

	protected void setPreparedStatement(PreparedStatement pstmt, Column column, Object value) throws SQLException {
		DbSaver saver = null;
		if (column.getDataTypeName() != null) {
			setColumnDataType(column);

			@SuppressWarnings("rawtypes")
			Class valueType = value != null ? value.getClass() : null;
			saver = typeMapping.lookup(valueType, column.getDataTypeName());
		}
		if (saver == null) {
			saver = NormalSaver.INSTANCE;
		}
		saver.save(value, pstmt, column);
	}

	@Override
	public MetaDataColumnHandler getMetaDataColumnHandler() {
		return this.metaDataColumnHandler;
	}

	@Override
	public void setMetaDataColumnHandler(MetaDataColumnHandler handler) {
		this.metaDataColumnHandler = handler;
	}

	@Override
	public ExecuteCallback getExecuteCallback() {
		ExecuteCallback callback = this.exeCallbackLocal.get();
		return callback != null ? callback : this.overallExecuteCallback;
	}

	@Override
	public void setExecuteCallback(ExecuteCallback callback) {
		setExecuteCallback(callback, false);
	}

	@Override
	public void setExecuteCallback(ExecuteCallback callback, boolean overall) {
		if (overall)
			this.overallExecuteCallback = callback;
		else
			this.exeCallbackLocal.set(callback);
	}

	@Override
	public TeniaHelper getHelper() {
		return this.helper;
	}

	@Override
	public void setHelper(TeniaHelper helper) {
		this.helper = helper;
	}

	public boolean getPlaceholderEmptyEqualsNull() {
		return this.placeholderEmptyEqualsNull;
	}

	public void setPlaceholderEmptyEqualsNull(boolean equals) {
		this.placeholderEmptyEqualsNull = equals;
	}

	@Override
	public void onLoad(ResultSetMetaData rsmd, int order, Column column) {
		setColumnDataType(column);
	}

	@Override
	public List<ProcedureMeta> listProcedure() throws SQLException {
		return listProcedure(this.catalog, this.schema, null);
	}

	@Override
	public List<ProcedureMeta> listProcedure(String catalog, String schema, String name) throws SQLException {
		ResultSet rs = null;
		Connection conn = this.connector.getConnection();
		List<ProcedureMeta> list = new ArrayList<ProcedureMeta>();
		try {
			rs = conn.getMetaData().getProcedures(catalog, schema, name);
			while (rs.next()) {
				ProcedureMeta meta = createProcedureMeta();
				meta.setCatalog(rs.getString("PROCEDURE_CAT"));
				meta.setSchema(rs.getString("PROCEDURE_SCHEM"));
				meta.setName(rs.getString("PROCEDURE_NAME"));
				meta.setType(rs.getShort("PROCEDURE_TYPE"));
				meta.loadColumns(this);
				list.add(meta);
			}
		} finally {
			CloseUtil.close(rs, conn);
		}
		return list;
	}

	public void setColumnDataType(Column column) {
		if (column != null)
			column.setDataType(getSqlType(column.getDataTypeName()));
	}

	protected abstract void setCatalog();

	protected abstract void setSchema();

	protected abstract ProcedureMeta createProcedureMeta();

	protected abstract ProcedureMeta createProcedureMeta(String procName);

	protected abstract ProcedureMeta createProcedureMeta(String catalog, String schema, String name);

	protected abstract DataTable getDataTable(Statement statement, ResultSet resultSet) throws SQLException;
}
